﻿using Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DAL
{
    public class UserService
    {
        //模糊查询
        public static List<UserModel> GetUserModelsBySearch(string str)
        {
            List<UserModel> list = new List<UserModel>();
            DataTable dt = DBHelper.GetDataTable($"select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id where c.name like '%{str}%'");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserModel user = new UserModel();
                user.cardid = dt.Rows[i]["cardid"].ToString();
                user.clientid = dt.Rows[i]["clientid"].ToString();
                user.pwd = dt.Rows[i]["pwd"].ToString();
                user.moneytype = dt.Rows[i]["moneytype"].ToString();
                user.serviceid = dt.Rows[i]["serviceid"].ToString();
                user.startdate = dt.Rows[i]["startdate"].ToString();
                user.moneynum = double.Parse(dt.Rows[i]["moneynum"].ToString());
                user.loserecord = bool.Parse(dt.Rows[i]["loserecord"].ToString());
                user.name = dt.Rows[i]["name"].ToString();
                user.phone = dt.Rows[i]["phone"].ToString();
                user.address = dt.Rows[i]["address"].ToString();
                list.Add(user);
            }
            return list;
        }

        //修改客户表信息
        public static bool UpdateClient(UserModel model)
        {
            return DBHelper.ExecuteSql($"update client set name='{model.name}',phone='{model.phone}',address='{model.address}' where id='{model.clientid}'");
        }

        //查询单个用户
        public static UserModel GetUserModelByClientid(string clientid)
        {
            DataTable dt = DBHelper.GetDataTable($"select * from client where id = '{clientid}'");
            UserModel user = null;
            if (dt.Rows.Count > 0)
            {
                user = new UserModel();
                user.clientid = dt.Rows[0]["id"].ToString();
                user.name = dt.Rows[0]["name"].ToString();
                user.phone = dt.Rows[0]["phone"].ToString();
                user.address = dt.Rows[0]["address"].ToString();
            }
            return user;
        }

        //删除单个账户
        public static bool DelAccountByCardID(string cardid)
        {
            return DBHelper.ExecuteSql($"delete account where cardid='{cardid}'");
        }

        //添加单个用户
        public static bool AddUser(UserModel model)
        {
            if (DBHelper.ExecuteSql($"insert client values('{model.clientid}','{model.name}','{model.phone}','{model.address}')"))
            {
                if (DBHelper.ExecuteSql($"insert account values('{model.cardid}','{model.clientid}',{model.pwd},1,1,GETDATE(),{model.moneynum},1)"))
                {
                    return true;
                }
            }
            return false;
        }

        //查询单个用户
        public static UserModel GetUserModelByCarIDAndPwd(string carID,string pwd)
        {
            try
            {
                //DataTable dt = DBHelper.GetDataTable($"select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id where a.cardid = '{carID}' and a.pwd = '{pwd}'");
                //1.使用存储过程预防SQL注入
                //DataTable dt = DBHelper.GetDataTable($"exec doLogin '{carID}',{pwd}");

                //2.使用参数变量来预防SQL注入
                string sql = "select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id where a.cardid = @cardid and a.pwd = @pwd";
                DBHelper.initConnection();//打开数据库
                DataTable dt = new DataTable();

                //2.1
                SqlCommand cmd = new SqlCommand(sql, DBHelper.conn);
                cmd.Parameters.AddWithValue("cardid", carID);
                cmd.Parameters.AddWithValue("pwd", pwd);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                //2.2
                //SqlDataAdapter adapter = new SqlDataAdapter(sql,DBHelper.conn);
                //adapter.SelectCommand.Parameters.AddWithValue("cardid", carID);
                //adapter.SelectCommand.Parameters.AddWithValue("pwd", pwd);

                adapter.Fill(dt);
                DBHelper.conn.Close();

                UserModel user = null;
                if (dt.Rows.Count > 0)
                {
                    user = new UserModel();
                    user.cardid = dt.Rows[0]["cardid"].ToString();
                    user.clientid = dt.Rows[0]["clientid"].ToString();
                    user.pwd = dt.Rows[0]["pwd"].ToString();
                    user.moneytype = dt.Rows[0]["moneytype"].ToString();
                    user.serviceid = dt.Rows[0]["serviceid"].ToString();
                    user.startdate = dt.Rows[0]["startdate"].ToString();
                    user.moneynum = double.Parse(dt.Rows[0]["moneynum"].ToString());
                    user.loserecord = bool.Parse(dt.Rows[0]["loserecord"].ToString());
                    user.name = dt.Rows[0]["name"].ToString();
                    user.phone = dt.Rows[0]["phone"].ToString();
                    user.address = dt.Rows[0]["address"].ToString();
                }
                return user;
            }
            catch (Exception)
            {
                return null;
            }
        }

        //获取所有用户列表(分页功能)
        public static List<UserModel> GetUserModelsWithPage(int pageIndex, int pageSize)
        {
            List<UserModel> list = new List<UserModel>();
            DataTable dt = DBHelper.GetDataTable($"select top {pageSize} * from user_view where id not in (select top (({pageIndex}-1)*{pageSize}) id from user_view)");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserModel user = new UserModel();
                user.cardid = dt.Rows[i]["cardid"].ToString();
                user.clientid = dt.Rows[i]["clientid"].ToString();
                user.pwd = dt.Rows[i]["pwd"].ToString();
                user.moneytype = dt.Rows[i]["moneytype"].ToString();
                user.serviceid = dt.Rows[i]["serviceid"].ToString();
                user.startdate = dt.Rows[i]["startdate"].ToString();
                user.moneynum = double.Parse(dt.Rows[i]["moneynum"].ToString());
                user.loserecord = bool.Parse(dt.Rows[i]["loserecord"].ToString());
                user.name = dt.Rows[i]["name"].ToString();
                user.phone = dt.Rows[i]["phone"].ToString();
                user.address = dt.Rows[i]["address"].ToString();
                list.Add(user);
            }
            return list;
        }

        //获取所有用户列表
        public static List<UserModel> GetUserModels()
        {
            List<UserModel> list = new List<UserModel>();
            DataTable dt = DBHelper.GetDataTable("select a.*,c.name,c.phone,c.address from account a join client c on a.clientid = c.id");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                UserModel user = new UserModel();
                user.cardid = dt.Rows[i]["cardid"].ToString();
                user.clientid = dt.Rows[i]["clientid"].ToString();
                user.pwd = dt.Rows[i]["pwd"].ToString();
                user.moneytype = dt.Rows[i]["moneytype"].ToString();
                user.serviceid = dt.Rows[i]["serviceid"].ToString();
                user.startdate = dt.Rows[i]["startdate"].ToString();
                user.moneynum = double.Parse(dt.Rows[i]["moneynum"].ToString());
                user.loserecord = bool.Parse(dt.Rows[i]["loserecord"].ToString());
                user.name = dt.Rows[i]["name"].ToString();
                user.phone = dt.Rows[i]["phone"].ToString();
                user.address = dt.Rows[i]["address"].ToString();
                list.Add(user);
            }
            return list;
        }
    }
}